# 代码 4-51
import pandas as pd
import numpy as np
from sqlalchemy import create_engine

detail = pd.read_excel('../data/meal_order_detail.xlsx', sheet_name='meal_order_detail1')
# print(detail)
# print(detail[['order_id','counts','amounts']].head(100))
detailGroup=detail[[ 'order_id','counts','amounts']].groupby(by='order_id')
# print("分组后的订单详情表格",detailGroup)
# print(detail.loc[detail['order_id']==137['order_id','dishes_name','count','amounts']])
#
# print(detailGroup.mean())
# print(detailGroup.max())
# print(detailGroup.min())
# print(detailGroup.count())
# print(detailGroup.size())
# print(detailGroup.sum())
# 代码 4-53
# print('订单详情表的菜品销量与售价的和与均值为：\n',
#       detail[['counts','amounts']].agg([np.sum,np.mean]))


# 代码 4-54
print('订单详情表的菜品销量总和与售价的均值为：\n',
      detailGroup.agg([np.sum,np.mean]))


# 代码 4-55
print('菜品订单详情表的菜品销量总和与售价的总和与均值为：\n',
      detailGroup.agg({'counts':np.sum,'amounts':[np.mean]}))
print("订单详情表的菜品销量预售价")
# 代码 4-53
print('订单详情表的菜品销量与售价的和与均值为：\n',
      detail[['counts','amounts']].agg([np.sum,np.mean]))


# 代码 4-54
print('订单详情表的菜品销量总和与售价的均值为：\n',
      detail.agg({'counts':np.sum,'amounts':np.mean}))


# 代码 4-55
print('菜品订单详情表的菜品销量总和与售价的总和与均值为：\n',
      detail.agg({'counts':np.sum,'amounts':[np.mean,np.sum]}))


# 代码 4-56
##自定义函数求两倍的和
def DoubleSum(data):
    s = data.sum()*2
    return s
print('菜品订单详情表的菜品销量两倍总和为：','\n',
      detail.agg({'counts':DoubleSum},axis = 0))
# 代码 4-57
##自定义函数求两倍的和
def DoubleSum1(data):
    s = np.sum(data)*2
    return s
print('订单详情表的菜品销量两倍总和为：\n',
      detail.agg({'counts':DoubleSum1},axis = 0).head())

print('订单详情表的菜品销量与售价的和的两倍为：\n',
      detail[['counts','amounts']].agg(DoubleSum1))
# 代码 4-61
print('订单详情表分组后前3组每组的均值为：','\n', detailGroup.apply(np.mean).head(3))
print('订单详情表分组后前3组每组的标准差为：','\n', detailGroup.apply(np.std).head(3))


# 代码 4-62
print('订单详情表的菜品销量与售价的两倍为：\n',
      detail[['counts','amounts']].transform(
            lambda x:x*2).head(4))
